package com.bsoft.gol.editor.utils;

import com.bsoft.gol.editor.editor.EditorUserInfo;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 注册统计导出excel
 *
 * @author sumu
 * @date 2019/7/1
 */
public class EditorAnswerExcelUtil {
    private final static Logger logger = LoggerFactory.getLogger(EditorAnswerExcelUtil.class);

    private static CellStyle style;

    /**
     * 生成汇总Excel
     *
     * @throws NullPointerException
     */
    public static byte[] getExcelAsByteArray(List<EditorUserInfo> list) {
        if (list == null) {
            throw new NullPointerException();
        }
        Workbook wb;
        ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
        try {
            wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("问卷列表");
            //基本样式
            initCellStyle(wb);
            //合并单元格
            initHeadStyle(sheet);
            //填充表头
            initHeadContent(sheet);
            //填充数据
            initBody(list, sheet);
            System.out.println("执行完成");
            wb.write(out);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return out.toByteArray();
    }

    private static void initBody(List<EditorUserInfo> list, Sheet sheet){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String date;
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i + 4);
            EditorUserInfo info = list.get(i);
            date = format.format(info.getCreateTime());
            setCellValue(row, 0, style, info.getUserName());
            setCellValue(row, 1, style, info.getPhoneNum());
            setCellValue(row, 2, style, date);
            setCellValue(row, 3, style, info.getQuestionnaireWay().toString());
            setCellValue(row, 4, style, info.getInvestigatorName());
        }
    }


    private static void initHeadContent(Sheet sheet) {
        Row row0 = sheet.createRow(0);
        Row row1 = sheet.createRow(1);
        Row row2 = sheet.createRow(2);
        Row row3 = sheet.createRow(3);
        setCellValue(row0, 0, style, "问卷统计列表");
        setCellValue(row2, 0, style, "姓名");
        setCellValue(row2, 1, style, "手机号");
        setCellValue(row2, 2, style, "时间");
        setCellValue(row2, 3, style, "问卷方式");
        setCellValue(row2, 4, style, "调查员姓名");

        row0.createCell(4).setCellStyle(style);
        row1.createCell(4).setCellStyle(style);

        row3.createCell(0).setCellStyle(style);
        row3.createCell(1).setCellStyle(style);
        row3.createCell(2).setCellStyle(style);
        row3.createCell(3).setCellStyle(style);
        row3.createCell(4).setCellStyle(style);
    }

    private static void initHeadStyle(Sheet sheet) {
        sheet.setColumnWidth(0, 15 * 256); // 设置列宽度
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);

        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));


    }

    private static void setCellValue(Row row, int column, CellStyle style, String value) {
        Cell cell = row.createCell(column);
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }

    private static void initCellStyle(Workbook wk) {
        style = wk.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
        style.setWrapText(true); //自动换行
        // 设置边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    }

    public static void main(String[] args) throws IOException {
        List<EditorUserInfo> list = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            EditorUserInfo info = new EditorUserInfo();
            info.setUserName("李易峰");
            info.setPhoneNum("13567898655");
            info.setCreateTime(new Date());
            info.setQuestionnaireWay((byte) 1);
            info.setInvestigatorName("张宇");
            list.add(info);
        }


        byte[] data = getExcelAsByteArray(list);
        OutputStream out = new FileOutputStream("D:\\tex.xlsx");
        out.write(data, 0, data.length);
    }
}
